﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyEmployees.Models;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace MyEmployees.DAL
{
    public class EmployeeService
    {
                     
        //获取雇员列表
        public IList<Employees> GetEmployeesList()
        {
            IList<Employees> employees = new List<Employees>();
            Employees emp;
            
            string sqlstr = "select EmployeeID,LastName,FirstName,Title,BirthDate,Address,City,Country from Employees";

            DataTable dt = DBHelper.GetDataTable(sqlstr);
            foreach (DataRow r in dt.Rows)
            {
                emp = new Employees();
                emp.EmployeeID = Convert.ToInt32(r["EmployeeID"]);
                emp.LastName = r["LastName"].ToString();
                emp.FirstName = r["FirstName"].ToString();
                emp.Title = r["Title"].ToString();
                emp.Birthday = Convert.ToDateTime(r["BirthDate"]);
                emp.Address = r["Address"].ToString();
                emp.City = r["City"].ToString();
                emp.Country = r["Country"].ToString();
                employees.Add(emp);
            }          
            return employees;
        }
    
        //删除雇员
        public int DeleteEmployee(int employeeID)
        {
            int result = 0;
            
            string sqlstr = string.Format("delete from Employees where EmployeeId={0}",employeeID);
            if(DBHelper.ExcuteCommand(sqlstr))
                result = 1;
    
            return result;
        }
    
        //判定记录是否存在
        public bool Exist(int employeeID)
        {
            bool result = false;
           
            string sqlstr = string.Format("select count(*) from Orders where EmployeeId={0}", employeeID);
            object obj = DBHelper.GetSingleData(sqlstr);
            result = Convert.ToInt32(obj)>0?true:false;
            return result;   
        }

        //根据ID值查找雇员
        public Employees SearchById(int employeeID)
        {
            Employees emp= new Employees();          
            string sqlstr = "select EmployeeID,LastName,FirstName,Title,BirthDate,Address,City,Country from Employees where EmployeeID="+employeeID;
                        
            DataTable dt = DBHelper.GetDataTable(sqlstr);
            if (dt.Rows.Count != 0)
            {
                emp.EmployeeID = Convert.ToInt32(dt.Rows[0]["EmployeeID"]);
                emp.LastName = dt.Rows[0]["LastName"].ToString();
                emp.FirstName = dt.Rows[0]["FirstName"].ToString();
                emp.Title = dt.Rows[0]["Title"].ToString();
                emp.Birthday = Convert.ToDateTime(dt.Rows[0]["BirthDate"]);
                emp.Address = dt.Rows[0]["Address"].ToString();
                emp.City = dt.Rows[0]["City"].ToString();
                emp.Country = dt.Rows[0]["Country"].ToString();    
                return emp;
            }
            else
            {
                return null;
            }       
        }

        //保存对雇员的修改
        public bool SaveEdit(Employees emp)
        {
            string sqlstr = string.Format("UPDATE Employees SET LastName='{0}',FirstName='{1}',Country='{2}',BirthDate='{3}' WHERE EmployeeID = {4}",
                    emp.LastName, emp.FirstName, emp.Country, emp.Birthday.ToShortDateString(), emp.EmployeeID);
            return DBHelper.ExcuteCommand(sqlstr);
        }

        //根据表中的ID取值自动生成新Id
        public int GetId()
        {
            int id;
            
            string sqlstr = string.Format("select max(EmployeeId) from Employees");            
            id = Convert.ToInt32(DBHelper.GetSingleData(sqlstr))+1;           
            return id;
        }

        //保存新员工信息
        public bool SaveAdd(Employees newemp)
        {
            string sqlstr = string.Format("Insert into Employees(LastName,FirstName,Country,BirthDate) Values(@LastName,@FirstName,@Country,@BirthDate)");
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@LastName",newemp.LastName),
                new SqlParameter("@FirstName",newemp.FirstName),
                new SqlParameter("@Country",newemp.Country),
                new SqlParameter("@BirthDate",newemp.Birthday.ToShortDateString())               
            };         
            return DBHelper.ExcuteCommand(sqlstr, param);         
        }        

    }
}
